
批量添加逗号="'"&A3&"',"     =VLOOKUP(C2,I:J,2,FALSE)
1. 插入行 insert  into customers(cust_id,cust_name
value('‘1000’,'tony')
插入检索出的数据  insert into customers(cust_id,cust_name,cust_city)从CustNew中将所有数据导入customers
                              select cust_id,cust_name  from   CustNew
    INSERT INTO 语句可以用两种方式编写。

     第一种：INSERT INTO table_name (column1, column2, column3, ...)

    VALUES (value1, value2, value3, ...);

    第二种：INSERT INTO table_name

VALUES (value1, value2, value3, ...);
2.update Customers   更新1005的email
 set Cust_email='kim'
    where cust_id='10005'
3.update Customers             更新1005的email
 set Cust_email='kim',            更新1005的email,contact    
        cust_contact='sam'           cust_contact='null'  除去值
    where cust_id='10005
select * into Cusscopy  from customers  customers表的整个内容复制到新表中
4.delete from  customers       删除
     where cust_id='10005'
5.desc (descending)关键字 降序
     ASC   默认升序    distinct ：            SELECT  distinct  country from websites 去掉  "country" 列重复值
6. 50 PERCENT  SELECT TOP 50 PERCENT  * FROM Websites;   
7.LIKE              SELECT * FROM Websites      选取 name 不包含模式 "oo" 的所有客户：
                       select  Prod_name,prod_name form Products  where prod_name like '%bean bag%'表示匹配包含bean bag的值
   NOT LIKE     WHERE name NOT LIKE '%oo%';
8.IN                SELECT * FROM Websites      选取 name 为 "Google" 或 "菜鸟教程" 的所有网站：
                       WHERE name IN ('Google','菜鸟教程');
9.BETWEEN    AND     SELECT * FROM Websites      选取 alexa 介于 1 和 20 之间的所有网站：
                                 WHERE alexa BETWEEN 1 AND 20;
10.  SELECT   INTO   SELECT *         只复制中国的网站插入到新表中：
                                INTO WebsitesBackup2016
                               FROM Websites
                               HERE country='CN';
11.    CREATE DATABASE my_db;

12.    CREATE TABLE 新表                              CREATE TABLE Persons
            AS                                                      (
           SELECT * FROM 旧表                          PersonID int,
                                                                     LastName varchar(255),
                                                                     FirstName varchar(255),
                                                                     Address varchar(255),
                                                                    City varchar(255)
                                                                      );
13. AVG() - 返回平均值                         AVG            SELECT site_id, count FROM access_log
     COUNT() - 返回行数                                           WHERE count > (SELECT AVG(count) FROM access_log);
     FIRST() - 返回第一个记录的值
     LAST() - 返回最后一个记录的值
     MAX() - 返回最大值
     MIN() - 返回最小值
     SUM() - 返回总和
14.NOT NULL                            CREATE TABLE Persons (                              ALTER TABLE Persons               创建的表的 "Age" 字段中添加 NOT NULL 约束
                                                  ID int NOT NULL,                                         MODIFY Age int NOT NULL;
                                                  LastName varchar(255) NOT NULL,
                                                  FirstName varchar(255) NOT NULL,
                                                   Age int
                                                   );
15.DROP TABLE table_name 
16. concat()函数   将多个字符串连接成一个字符串  concat('http://192.168.1.169:8080',substr(SOURCE,30,99)) SOURCE
select pmn31t  from  apmt540a_s_pmn a where  pmn04='ZFHC7003000014' and pmn33 in(select max(pmn33) from  apmt540a_s_pmn a where  pmn04='ZFHC7003000014')
17.  查询时间段：
WSC11 BETWEEN to_date('2021/7/12','YYYY/MM/DD')  AND  to_date('2021/8/12','YYYY/MM/DD')
18.
    INNER JOIN   on 条件：如果表中有至少一个匹配，则返回行
    LEFT JOIN     on 条件：即使右表中没有匹配，也从左表返回所有的行
    RIGHT JOIN  on 条件：即使左表中没有匹配，也从右表返回所有的行
    FULL JOIN    on 条件：只要其中一个表中存在匹配，则返回行
19.
ORACLE时间函数(SYSDATE)深入理解

加法 
select sysdate,add_months(sysdate,12) from dual; --加1年 
select sysdate,add_months(sysdate,1) from dual; --加1月 
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期 
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天 
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时 
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟 
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒 

减法 
select sysdate,add_months(sysdate,-12) from dual; --减1年 
select sysdate,add_months(sysdate,-1) from dual; --减1月 
select trunc(add_months(sysdate,-1)) from dual;  --减1月
select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期 
select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天 
select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --减1小时 
select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟 
select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒 
--求工厂人平均年龄
select   cpf03, avg(TO_CHAR(SYSDATE,'yyyy')-substr(to_char(cpf04,'yyyymmdd'),0,4)) age from  cpf_file 
group by cpf03 
20.链接符的应用：||        tc_cqs01||tc_cqs02 in (select rvv31||rvv34 from rvv_file )
                                     TC_ADE06>to_date(to_char(sysdate -7,'yyyy/MM/dd'),'yyyy/MM/dd') 
    avg(TO_CHAR(SYSDATE,'yyyy')-substr(to_char(cpf04,'yyyymmdd'),0,4)) 
       substr(to_char(cpf04,'yyyymmdd'),0,4)   --截取字符
       TO_CHAR(SYSDATE,'yyyy')                     --转换日期
21.trunc 函数：截取日期或数字    TRUNC（89.985，2）=89.98    TRUNC（89.985）=89 (即取整)   TRUNC（89.985，-1）=80
       截取日期年份：Select to_number(to_char(sysdate,'yyyy')) from dual
                 
                1.获取当前年月日
                trunc(sysdate)或者trunc(sysdate,'dd')
                2.当年第一天
                 trunc(sysdate,'yyyy')
                 3.当月第一天
                trunc(sysdate,'mm')
                4.当周第一天
                trunc(sysdate,'d')
                     select  trunc(sysdate,'yyyy')  from dual
                   ---Select Extract(year from cpf04) from cpf_file  数值型
            to_date(Extract(year from sysdate)||substr(to_char(ta_cpf011d,'yyyymmdd'),5,4),'yyyy/mm/dd')  
      
    AND  TO_CHAR(cza02,'yyyy/mm/dd')= TO_CHAR(SYSDATE+1,'yyyy/mm/dd') 

     每周： TRUNC(next_day(sysdate,'星期五'))+10/24
     加一天：TRUNC(SYSDATE+1)+15/24+00/1440
    case：
   select case a when 1 then 'one'
   when 2 then 'two'
   else 'null' end,
   case when a = 1 then'one'
   when a=2 then 'two'
   else 'null'
   end
   from tab
   select case when d.CURRENTSTATE='1' then '进行中' else '完成' end  状态  from  PROCESSINSTANCE@PD102_BPM d
22.修改表名alter table 表名 rename to 新表名；
    添加一列alter table  表名 add 列名 数据类型；
    修改数据类型alter table表名 modify 列名 新数据类型
    修改列名和数据类型 alter table 表名 change 列名 新列名 新数据类型
    删除列 alter table 表名 drop 列名；
Oracle数据库中查询所有触发器、存储过程、视图、表
23. select nvl(sum(t.dwxhl),1)   就表示如果sum(t.dwxhl) = NULL 就返回 1
方法一：

Select object_name From user_objects Where object_type='TRIGGER';  --所有触发器

Select object_name From user_objects Where object_type='PROCEDURE';  --所有存储过程

Select object_name From user_objects Where object_type='VIEW';  --所有视图

Select object_name From user_objects Where object_type='TABLE'; --所有表

方法二：

方法二：

当然，还可以试试下面的语句

Select * From user_triggers; --所有触发器

Select * From user_procedures; --所有存储过程

Select * From user_views; --所有视图

Select * From user_tables; --所有表
selecT  *  from USER_JOBS;--所有JOB 

第一种方法:
   判断字段是否为空，如果为空转成你要的字符
24.oracle :
nvl(“字段名”，’转换后的值’)；//字段名是双引号，转换后的值是单引号
2.sql Server:
isnull(“字段名”,’转换后的值’)//字段名是双引号，转换后的值是单引号
3.mySql:
ifnull(字段名，’转换后的值’)//字段名不加引号，转换后的值是单引号
注：mySql 也有isnull  但是只判断并不会转换
25.rownum<2   :取第一行的数据
26.行转列：语法：

Select * From 表名-- 这个表应该是下面聚合之后的表，而不是原理的行表
PIVOT
(
  SUM('要合并的列1'),MAX('要合并的列2'),.... --操作的列

FOR

 将值转换成列的列名 IN
(列值1，列值2，列值3，列值4....）
)
 1.  case when .......then else  ....end 用法，行列转换*/
 SELECT  Student AS '姓名' ,
         MAX(CASE Subject
               WHEN '语文' THEN Score
               ELSE 0
             END) AS '语文' ,--如果这个行是“语文”，就选此行作为列
        MAX(CASE Subject
               WHEN '英语' THEN Score
               ELSE 0
             END) AS '英语'
FROM    Scores
 GROUP BY Student
 ORDER BY Student

/*2. pivot(聚合函数(要转成列值的列名)
　      for 要转换的列
 　　    in（目标列名）
 　　)*/

SELECT  Student AS '姓名' ,
         AVG(语文) AS '语文' ,
         AVG(英语) AS '英语'
FROM    Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScores
 GROUP BY Student
 ORDER BY Student ASC
———————————————
27.替换
replace(‘将处理的字符串’,‘搜索的关键字’,‘最终替换的字符’)
select replace('I Can Make IT! Just do IT!','IT','信息技术') from dual;
I Can Make 信息技术! Just do 信息技术!  
28.Oracle函数内部使用insert插入      Oracle函数更新PRAGMA AUTONOMOUS_TRANSACTION;COMMIT，procedure更新
-------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION changePY 
  RETURN VARCHAR2
as
PRAGMA AUTONOMOUS_TRANSACTION;  --as 或者 is 后边 加上这句  PRAGMA AUTONOMOUS_TRANSACTION “自治事务”
  v_length INT := 0;
  T_ICD10VALUE  varchar2(1000);
  T_ICD10code  varchar2(1000);
  T_ICDTYPE  varchar2(1000);
  T_ICDBM  varchar2(1000);
  T_ICDSAU  varchar2(1000);
  T_ICDOLDCODE  varchar2(1000);
  T_ICDOLDVALUE  varchar2(0100);
  T_REMARK  varchar2(1000);
  T_PYM  varchar2(1000);
  T_PYMA  varchar2(1000);
      CURSOR DataTable IS select * from  icd10v2;
   begin
   select count(*) into v_length  from icd10v2;
   open DataTable;
   loop
     FETCH  DataTable into T_ICD10code, T_ICD10VALUE, T_ICDTYPE, T_ICDBM, T_ICDSAU,T_ICDOLDCODE, T_ICDOLDVALUE, T_REMARK, T_PYM;
      exit when DataTable%notfound;   
        T_PYMA:=fn_getpy(T_ICD10VALUE,4) ;
        update icd10v2 set PYM=T_PYMA  where ICD10CODE=T_ICD10code and  ICD10VALUE=T_ICD10VALUE;
        COMMIT; -- 在更新语句执行之后提交
  end loop;
  CLOSE DataTable;
  return '更新成功';  
end changePY;
----------------------------------------------------------------------------------
  SELECT SF_PY_APYT103104(g_cqh[l_ac].ta_cqh04,g_cqh[l_ac].cqh01,'C') INTO l_ta_cza571 FROM DUAL  # 可休
  SELECT SF_PY_APYT103104(g_cqh[l_ac].ta_cqh04,g_cqh[l_ac].cqh01,'B') INTO l_ta_cza572 FROM DUAL  # 已请

  CREATE OR REPLACE FUNCTION SF_PY_APYT103104 (P1    VARCHAR2,
                                             P2    NUMBER,
                                             P3    VARCHAR2,
                                             P4    DATE)
   RETURN NUMBER
IS
   v_ta_cza20   NUMBER (8, 4);
   v_azf03      NUMBER (8, 4);
BEGIN
   IF P3 = 'A'                                          --- 每月申请加班时数--
   THEN
      SELECT   to_char(azf03) INTO v_azf03
        FROM   azf_file
       WHERE   azf02 = 'Z2' AND AZF01 = 'apyt103' AND azfacti='Y' ;
       
      IF MONTH (P4) = 1 AND P2 =1 THEN
          SELECT   NVL (SUM (TA_CZA12), 0)
            INTO   v_ta_cza20
            FROM   cza_file, czaa_file
           WHERE       cza01 = P1
                   AND (MONTH (CZA02) = 12  AND YEAR (CZA02) = YEAR (P4)-1 )
                   AND czaaconf <>'X' AND (ta_cza52='1' OR ta_cza52='S')
                   AND cza011 = czaa01;  
      ELSIF   MONTH (P4) = 1 AND P2 =2 THEN
          SELECT   NVL (SUM (TA_CZA12), 0)
            INTO   v_ta_cza20
            FROM   cza_file, czaa_file
           WHERE       cza01 = P1
                   AND (MONTH (CZA02) = 11  AND YEAR (CZA02) = YEAR (P4)-1 )
                   AND czaaconf <>'X' AND (ta_cza52='1' OR ta_cza52='S')
                   AND cza011 = czaa01;  
      ELSIF   MONTH (P4) = 2 AND P2 =1 THEN
          SELECT   NVL (SUM (TA_CZA12), 0)
            INTO   v_ta_cza20
            FROM   cza_file, czaa_file
           WHERE       cza01 = P1
                   AND (MONTH (CZA02) = 1  AND YEAR (CZA02) = YEAR (P4) )
                   AND  czaaconf <>'X' AND (ta_cza52='1' OR ta_cza52='S')
                   AND cza011 = czaa01; 
      ELSIF   MONTH (P4) = 2 AND P2 =2 THEN
          SELECT   NVL (SUM (TA_CZA12), 0)
            INTO   v_ta_cza20
            FROM   cza_file, czaa_file
           WHERE       cza01 = P1
                   AND (MONTH (CZA02) = 12  AND YEAR (CZA02) = YEAR (P4)-1 )
                   AND  czaaconf <>'X' AND (ta_cza52='1' OR ta_cza52='S')
                   AND cza011 = czaa01;                    
      ELSE                 
          SELECT   NVL (SUM (TA_CZA12), 0)
            INTO   v_ta_cza20
            FROM   cza_file, czaa_file
           WHERE       cza01 = P1
                   AND MONTH (CZA02) = MONTH (P4) - P2  AND YEAR (CZA02) = YEAR (P4)  
                   AND  czaaconf <>'X' AND (ta_cza52='1' OR ta_cza52='S')
                   AND cza011 = czaa01;     
      END IF;      
               
   ELSIF P3 = 'B'                                       --- 每月申请请假时数--
   THEN       
      SELECT   NVL (SUM (CQH07), 0)
        INTO   v_ta_cza20
        FROM   CQG_FILE, CQH_FILE
       WHERE       TA_CQG01 = TA_CQH01
               AND CQG11 = '1'
               AND CQGCONF = 'Y'
               AND cqh01 = P1
               AND MONTH (CQH04) = MONTH (P4) - P2 AND YEAR (CQH04) = YEAR (P4);

   ELSIF P3 = 'C'                        --- 每年加班时数--
   THEN 
      SELECT   NVL (SUM (TA_CZA20), 0)
        INTO   v_ta_cza20
        FROM   cza_file, czaa_file
       WHERE       cza01 = P1
               AND YEAR (CZA02) = YEAR (P4)  AND MONTH (CZA02) = MONTH (P4) 
               AND czaaconf='Y' AND ta_cza52='1'
               AND cza011 = czaa01;

   END IF;
   
   RETURN v_ta_cza20;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN '';
END;

create or replace function GET_PRODUCT_ID_BY_NAME(productName varchar2)

return number is

pragma autonomous_transaction;

mResult number; -- 返回结果ID

countResult number; -- 根据名称查询结果

tempVar varchar2(200);-- 临时处理字符

begin

tempVar := trim(productName);

select count(TPI.ID)

into countResult

from TBL_PRODUCT_INFO TPI

where TPI.PRODUCT_NAME = tempVar;

if (countResult = 0) then -- 产品不存在

select seq_tbl_product_info.Nextval into mResult from dual;

insert into TBL_PRODUCT_INFO

(ID, PRODUCT_NAME)

values

(mResult, tempVar);

commit;

else -- 产品已存在

select TPI.ID

into mResult

from TBL_PRODUCT_INFO TPI

where TPI.PRODUCT_NAME = tempVar;

end if;

return(mResult);

end;
29.  listagg (字段, ',') WITHIN GROUP (ORDER BY T .ENAME)?    group by cqg01
     将多行记录的某个字段在一行显示，group(order by t)是这一行内容的排序
  select cqg01,listagg(CQG03,',') within group (order by cqg03) as cqg03S   group by cqg01
30.临时表 例子
    DROP TABLE x
 
   SELECT * FROM zxw_file WHERE zxw01=g_zxw01 INTO TEMP x
 
   UPDATE x SET zxw01=l_newno,     #資料鍵值
                ta_zxw08=''         # MIS-LS221200026   add by dq
 
   INSERT INTO zxw_file SELECT * FROM x
 
   IF SQLCA.sqlcode THEN
      CALL cl_err3("ins","zxw_file",l_newno,"",SQLCA.sqlcode,"","",0)   
      RETURN
   END IF
31.子查询：
    with area as (
    select cpf01 from cpf_file where cpf22 like '%晨星%'
     )
    ,avgage as (
     select avg(to_number(to_char(cpf04,'yyyy')))  as avgagea from cpf_file where cpf22 like '%晨星小区%'
      )
    select cpf01,cpf02 from cpf_file
      where  cpf01 in (select cpf01 from area)
      and to_number(to_char(cpf04,'yyyy'))<(select avgagea from avgage)


SQL Server 2005基础知识整理

1. ACID：指数据库事务正确执行的四个基本要素缩写：1.原子性2.一致性3.隔离性4.持久性

2.数据库对象：表（table） 视图（view） 存储过程（stored procedure） 函数（function）索引（index）

3.SQL Server 2005中包含master、model、msdb、tempdb四个系统数据库。

4.使用T-SQL语句创建数据库：

CREATE DATABASE [ApressFinacial] ON  PRIMARY 

( NAME = N'ApressFinacial', FILENAME = N'I:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ApressFinacial.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON 

( NAME = N'ApressFinacial_log', FILENAME = N'I:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ApressFinacial_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

5.T-SQL语句删除数据库：drop database[数据库名]

6.常见的约束类型：主键约束、唯一约束、检查约束、默认约束、外键约束。

7.添加约束的语法：

         Alter table 表名

         Add  constraint  约束名 约束类型 具体的约束说明

8.删除约束：alter table 表名

            Drop constraint 约束名

9.授权语法：  grant 权限[on  表名] to 数据库用户 

         例：grant  select  insert  update

              On students  to  zhangsan

10.T-SQL语句创建登录名：

           Create  login[yuan]

            With  password='130125'

            Go

11.数据库完整性分为：实体完整性、域完整性、参照完整性、用户自定义完整性。

12.T-SQL 中的运算符：

运算符
	

含义

=
	

等于

>
	

大于

<
	

小于

>=
	

大于等于

<=
	

小于等于

<>
	

不等于

!
	

非

通配符
	

解释
	

示例

_
	

一个字符
	

A Like 'C_'

%
	

任意长度的字符
	

B Like 'CO_%'

[]
	

括号中所指定范围内的一个字符
	

C Like '9W0[1-2]'

[^]
	

不在括号中所指定范围内的一个字符
	

D Like '%[A-Z][^1-2]'

13.查询语句：select 列名  from 表名  where 条件   如：select*  from students  where       no=1

14.插入数据行：insert  [into]<表名> [列名]values<值列表>

       例：intset into students(name,address,grade,email,sex)

           Values('长青菜','上海松江',5,'zqc@souhu.com','男')

      注意：（不能为标识列指定值，因为它的数字是自动增长的）

15.更新数据行：update <表名> set<列名=更新值>    [where<更新条件>]

      例：update  students

          Set  address='石家庄'

          Where   address='保定'

16.删除数据行：  delete  from<表名>[where<删除条件>]

       例：delete from  studentinfo     where  no='180108225'

17.问题：如果标识列A的初始值为1，增长量为2，则输入三行数据以后，再删除两行，下次再输入数据行的时候，标识值从多少开始？      (  7  )

18.删除列： alter  table  student     drop  column  address

19.Exists：     if  exists (select*from?where  name='yuanDB')  drop  database stuDB

20.变量：   局部变量（必须以标记@作为前缀）

            全局变量（必须以标记@@作为前缀）

       全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值。

21.局部变量声明：  例：Decalre  @name  vachar(8)      decare @seat  int

   赋值：set @变量名=值或select@变量名=值  set @name='张三'

           Select @name=sname  from  students  where  no='1'

22.逻辑控制语句： IF-ELSE语句

            If(条件)

              Begin

              语句1

              语句2...........

              End

            Else

            Begin

            语句1

            语句2.........

            End        注：else是可选的。

例： decalre @myavg float

     Select @myavg =AVG(course) from score

     Print'平均分'+convert(varchar(5),@myavg)

     If(@myavg>70)

     Begin

     Print '本班成绩优秀，前三名成绩为：'

      Select  top 3 * from  score  order  by score  desc

      End

      Else

        Begin 

        Print  '本班成绩较差，后三名成绩为：'

        Select  top 3 *from  score  order  by  score

       End

23.while循环语句：   while（条件）

                     Begin 

                     语句1

                     语句2..........

                     Break

                     End

       例：DECLARE @n int

           WHILE(1=1) --条件永远成立

           BEGIN

           SELECT @n=COUNT(*) FROM score 

           WHERE score<60    --统计不及格人数

           IF (@n>0)

           UPDATE score     --每人加2分

            SET score=score+2 

          ELSE

          BREAK    --退出循环

          END

          print '加分后的成绩如下：'

          SELECT * FROM score

24.case--end多分支语句

      语法：    case

                When  条件1  then  结果1

                When  条件2  then  结果2

                ..............

                 Else  其他结果

                 End

       例：print  'ABCDE五级显示成绩如下：'

           Select  studentID,

               成绩=case

                        When  score<60  then'E'

                        When   score  between  60  and  69  then 'D'

                        When   score  between  70  and  79  then 'C'

                         Else  'A'

                      End

                     From  score

25.批处理语句示例：

                   SELECT * FROM stuInfo

                   SELECT * FROM stuMarks

                   UPDATE stuMarks 

                      SET writtenExam=writtenExam+2 

                   GO

26.习题及答案：    

        习题内容：则根据如下规则对成绩进行反复加分，直到平均分超过85分为止。请编写T-SQL语句实现。

90分以上：   不加分

80－89分：   加1分

70－79分：   加2分

60－69分：   加3分

60分以下：   加5分

         习题答案：

SELECT * FROM score --原始成绩

DECLARE @labAvg  int

WHILE(1=1)

 BEGIN

    UPDATE score

      SET score=

        CASE

            WHEN score<60 THEN score+5

            WHEN score between 60 AND 69 THEN score+3

            WHEN score between 70 AND 79 THEN score+2 

            WHEN score between 80 AND 89 THEN score+1

            ELSE score

        END

     SELECT @labAvg=AVG(score) FROM score

       IF  @labAvg>=85

           BREAK

 END

SELECT * FROM score --加分后的成绩

27.把一个表中的数据复制到另一个表中：  insert  into <表名>（列名）

                                       Select  <列名>

                                       From <源表名>

28.把查询结果放在新表中同时插入新的标识列：

           Select  列名  identity（数据类型，标识种子，标识增长量）as

            列名  into  新表    from  原始表

  例：   select   students.sname, students.saddress,  students.semail,  identity(int,1,1) as  studentID    into  tongxunlu    from students

29.更新数据行：  

      语法：  update<表名> 

              Set<列名=更新值>

              Where <更新条件>

       例： update  students

             Set   address='软件学校'

              Where  address='软件学院'

30.问题：按多列排序：例：select  no,name,address,grade

                   From   students

                   Where   sex  in(1,0)

                   Order by   no,grade

31.字符串函数：

      Replace:    select  replace('莫勒可切.杨可'，'可'，‘兰’)

          返回结果：莫勒兰切.杨兰

      Upper:    select  upper('sql server 课程')

       返回结果：SQL SERVER 课程

      Getdate：   select  getdate（）获取当前时间。

       Dateadd：   select  dateadd（mm,4,'01/01/2009'）

        返回结果：05/01/2009

      Datename:     select  datename(dw,'01/15/2000')

       返回结果：   Saturday

32.聚合函数：

         Max-min:     select  AVG(score) as  平均成绩,MAX(score) as 最高分,MIN(score) as最低分       from  score          where  score>=60

         Count：    select  count（*）as  及格人数      from  score  

                      Where   score>=60

33. 分组查询：  select  courseid,avg(score) as 课程平均成绩

                From  score

                Group  by  courseid

34.习题：   

          假如成绩单中记录了学生的几次考试成绩，要查询每位学生的每门课的总成绩，        怎么查？        Select  courseid，studentid，sum（score） as 总成绩

                From  score

                Group  by  courseid，studentid

35.分组查询--HAVING

             Having：指定了组或聚合的搜索条件，常常与group by子句一起使用。

           例：   select  studentid  as学员编号，avg（score）as 平均成绩

                  From   score

                  Group  by  studentid，couseid

                  Having  avg（score）>60

36.多表查询——内联结     例：select  students.smame, score.courseid, score.score

                              From  students,score

                              Where  students.scode=score.studentid

37.多表联结——三表联结     例：select  S.name  as姓名，CS.coursename as课程,C.score as 成绩                         from  students  As S

                                Inner Join score  as C  ON（S.scode=C.studentid）

                                Inner join  course  As CS  ON(CS.courseid=C.courseid)

38.多表联结查询——右外联结   

           例：select  titles.title_id,Titles.title,publishers.pub_name

               From  titles

               Right outer  join  publishers

               On  titles.pub_id=publishers.pub_id

39.银行案例——多表联结——左外联结：

            例：intsert  into  account（account.Cardid，account.score）

                Select  users.cardid，2 

                From users 

                Left  Join account  ON（account.cardid=users.cardid）

                Where  account.cardid  is NULL

40.子查询：  例：select * from  students

                 Where  scode>(select  scode  from  students  where  Sname='张扬')  

                 GO

41.例：内联结（等值联结）  select  sname  from  students

                           Inner  Join  score

                           ON  students.scode=score.studentid

                           Where  score=60           GO 

42.局部变量：例：将姓名为里斯的学生的学号，出生日期和平均成绩分别赋给局部变量@no_str @birthday_str @avgrade_num  （用select语句来实现）

             Select  @no_str=s_no,@birthday_str=s_birthday,@avgrade_num=s_avgrade

              From student          where  s_name=’里斯’

43.自连接查询：     例：（注：student2为不存在，是创建的）

               Select  student2.sno学号，student2.sname姓名，student2.ssex性别，student2.sspeciality专业，student2.savgrade平均成绩

               From student，student2

               Where  student.sname=’李好’ and student.sspeciality=student2.sspeciality 

44．声明局部变量：declare  @变量名  数据类型

45.if语句       例：查询学号为457865的学生，如果成绩及格，则打印其姓名和成绩。

         Declare @no,char(8),@name char(8),@avgrade numeric(3,1)

         Set @no=’457865’

         Select @name=sname,@avgrade=savgrade

         From student

         Where sno=@no;

         If @avgrade>60.0

         Begin

         Print @name

         Print @avgrade

         End

         Go

46.if-else语句：对于给定学号的查询，如果平均成绩不及格则打印姓名和平均成绩，否则打印学号。

          Declare @no char(8),@name char(8),@avgrade numeric(3,1);

          Set @no=’567’;

          Select @name=sname,@avgrade=savgrade

          From student

          Where  sno=@no;

          If @avgrade<60.0

          Begin

          Print @name

          Print @avgrade

          End

          Else

          Print @no

          Go

47.多分支的if语句查询并实现分等级打印学生成绩

          Declare @no char(8),@name char(8),@avgrade numeric(3,1);

          Set @no=’567’;

          Select @name=sname,@avgrade=savgrade

          From student

          Where  sno=@no;

          If @avgrade>=90.0

          Print ‘优秀’

          Else if @avgrade>=80.0

          …………….

          Else print ‘不及格’

          Go

48.case 语句   使用select语句查询学生的学号，然后用case函数对学生的姓名和爱好进行简要说明。    Select  学号=sno，姓名及爱好=

              Case sno

              When ‘123’ then ‘李好，游泳’

              。。。。。。。

              Else ‘没有这人’

              End

              From  student 

49.while语句

      （如果学生平均成绩低于85分，则循环执行对每位学生成绩加0.5%，在循环过程中，如果最高成绩超过95分则退出循环；在加分过程中，当成绩的平均成绩大于或等于80分时打印出当前成绩的平均成绩）

            Declare @avgrade numeric（3，1）；

            Set @avgrade=（select  avg（savgrade） from student）

            While @avgrade<85

             Begin 

                      Update student

                        Set savgrade=savgrade+savgrade*0.005

                      Set @avgrade=（select avg（savgrade） from student）

                      If @avgrade>95

                         Break                            --退出循环

                      if@avgrade<80

                      continue                             --结束本次循环

                      print @avgrade

                      End

50.子查询：查询成绩刚好通过60分的学生

              Select sname from students

                  Inner  Join  score                ---内连接

                   ON students.scode=score.studentid

                   Where  score=60

                    Go

51.事务的特性：原子性、一致性、隔离性、永久性。

  事务分类：显示事务、隐性事务、自动提交事务。

52.系统存储过程的名称都以“sp_”开头或“xp_”开头。

        EXEC sp_databases     列出当前系统的数据库

        EXEC sp_help students  查看表students的信息

53.定义存储过程的语法：   

             Create  proc[edure]  存储过程名

              @参数1  数据类型=默认值 output，

               ...................

              @参数n  数据类型=默认值 output

              As

              SQL语句

              Go （注：参数可选，参数分输入参数、输出参数，输入参数允许有默认值） 

54.创建不带参数的存储过程：     例：

           Creat  procedure  pro_stu

           As

           Declare  @myAvg float

           Select  @myAvg=AVG（score）

           From score

           Print '平均分：'+convert（varchar（5），@myAvg）

           If（@myAvg>70）

              Print '本班考试成绩：优秀' 

            Else

               Print '本班考试成绩：较差' 

            Print '-----------------------------------------------------------' 

            Print '参加本次考试没有通过的学员：' 

               Select  name，scode，score

                   From students inner Join score  on 

                   Students.scode=score.studentid     where  score<60        GO 

55.调用存储过程：   语法：   EXEC  过程名[参数]    例：EXEC proc_stu  

56.输入参数的默认值:     例：

         Create procedure  proc_stuMn

            @scorePass  int=60        ---及格线默认为60分

         As 

            Print '及格线：' +convert(varchar(5),@scorePass)

            Print '参加本次考试没有通过的学员：' 

          Select  name，scode，score

                From students

                Inner Join score 

                On students.scode=score.studentid

                 Where score<@scorePass

         Go 

57.调用带参数默认值的存储过程：  

             EXEC proc_stu    -------采用默认值

             EXEC proc_stu 55    ------不采用默认值

             EXEC proc_stu  @scorePass=55

58.带输出参数的存储过程：

        Create procedure proc_stuM

        @notpassSum  int  output，

        @scorePass  int=60

        As  

           Print'及格线：'+convert（varchar（5），@scorePass）

           Print '参加本次考试没有通过的学员：'

        Select  name，scode，score      from  students

              Inner Join score  On   students.scode=score.studentid

                   Where  score<@scorePass

         Select @notpassSum=count（studentid）

               From score where score<@scorePass

         GO

59.调用带输出参数的存储过程：（注：调用时必须带output关键字，返回结果将存放在变量@sum 中）

           Declare @sum int

           EXEC proc_stuM @sum output，60

           Print '--------------------------------------------------'

           If @sum>=3

                 Print '未通过人数：'+convert（varchar（5），@sum ）+'人，超过60%，及格分数线还应下调'

           Else 

                  Print '未通过人数：'+convert（varchar（5），@sum ）+'人，已控制在60%以下，及格分数线适中'           

           GO

60.错误处理：   

                   使用Raiserror语句：

           Create  procedure  pro_stuM

                  @notpassSum  int output，

                  @scorePass  int=60

            As

               If（not  @scorePass  between 0 and 100）

                   Begin

                   Raiserror（'及格线错误，请指定0—100之间的分数，统计中断退出'，16，1） --------引发系统错误，指定错误的严重级别16，调用状态为1（默认），并影响@@ERROR 系统变量的值

                   Return   --------立即返回，退出存储过程

             End 

               ...............其他语句同上例

             Go 

61.使用raiserror语句

          Declare  @sum int，@t int

          EXEC proc_stum @sum  output, 604   ----笔试及格线误输入604分

          Set @t=@@error

          Print '错误号：'+convert（varchar（5），@t ）

          If @t<>o

          Return   ------退出批处理，后续语句不再执行

          Print '-----------------------------------'      -----如果执行了raiseerror语句，系统全局@@error 将不等于0，表示出现了错误.

           If @sum>=3

             Print '未通过人数：'+convert(varchar(5),@sum)+'人，超过了60%，及格分数线还应下调'

           Else   print '未通过人数：'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分数线适中'              GO 

62.调用存储过程：          例：

               Declare  @s  int

               Exec proc_stuinfo  '张三' ，@s ， 30    ———错误！应为（exec proc_stuinfo '张三'，@s  output，30）

                Exec proc_stuinfo '张三' ，@s     ————错误！应为（exec proc_stuinfo '张三'，@s output）

                Exec proc_stuinfo  @sAge=22，@stuName='李四'，@m=@s output（正确！）

63.带输出参数的存储过程：   例：

           Declare @sum  int

           Exec proc_stuM @sum  output，60

           Print '-----------------------------------------'

           If  @sum>=3

              Print'未通过人数：'+convert（varchar（5），@sum ）+'人，超过60%，及格分数线还应下调'

           Else  

               Print'未通过人数：'+convert（varchar（5），@sum ）+'人，已控制在60…60%以下，及格分数线适中'       GO


64.创建触发器语法：
                          CREATE OR REPLACE TRIGGER TRG_CQI
                          BEFORE INSERT
                                OR
                          UPDATE
                                ON CQI_file for each row

                          BEGIN
                          IF INSERTING THEN --INSERT触发
                          INSERT INTO CQI_log VALUES(
                              :NEW.cqi01,

                              :NEW.cqi07,
                              :NEW.ta_cqi07,
                              :NEW.ta_cqi01,
                              'INSERT',
                              :NEW.CQIMODU,
                              SYSDATE
                          );

                          ELSIF UPDATING and  (:new.cqi07<>:old.cqi07 or :new.ta_cqi07<>:old.ta_cqi07 OR  :new.ta_cqi01<>:old.ta_cqi01 ) THEN --UPDATE触发

                          INSERT INTO CQI_log VALUES(
                              :NEW.cqi01,
                              :OLD.cqi07,
                              :OLD.ta_cqi07,
                              :OLD.ta_cqi01,
                              'UPDATE',
                              :NEW.CQIMODU,
                              SYSDATE
                          );
                          END IF;
                          END;   
              delete,update前用old;
                            insert,update后用new
                            old,new都是代表当前操作的记录行。

              old是代表要操作的更改前的记录，new是代表要操作的更改后的记录。 
 Create  trigger  trigger_name

                       On  table_name

                       [with  encryption] --------表示加密触发器定义的SQL文本

                       For [delete,insert,update]    -------指定触发器类型

                       As 

                         T_SQL语句

                       GO    

65.Insert 触发器   例：

       Create trigger  trig_transInfo

       On  transInfo

        For  insert

        As 

        Declare  @type  char（4），@outMoney  Money

        Declare  @myCardID  char（10），@balance  Money

        Declare  @currentMoney  money

        Select  @type=transType,@outMoney=transMoney,@myCardID=cardID from inserted

              If(@type='支取')

                   Update  bank  set  currentMoney=currentMoney-@outMoney

                   Where  cardID=@myCardID

              Else  

                   Update  bank  set  currentMoney=currentMoney+@outMoney

                   Where  cardID=@myCardID

66. 插入一条语句     例：

          Insert  into  transInfo（transType，transMoney，cardID）

          Values （'支取'，200，'10011001'）

67. Delete触发器示例：

            Create  trigger  tri_delete_transInfo

            On  transInfo

            For  delete

            As 

                Print '开始备份数据，请稍候......'

                If  not  exists（select * from  sysobjects  where  name='backupTable'）

                Select * into backupTable  from  deleted        ------------从deleted表中获取被删除的交易记录。

                 Else 

                    Insert  into backupTable  select * from  deleted 

                    Print '备份数据成功，备份表中的数据为：'

                    Select * from  backupTable 

                    Select * from  transInfo

                GO    

68. Update 触发器：

            Create  trigger  trig_update_bank

            On  bank 

            For  update

            As  

                Declare  @beforeMoney  Money，@afterMoney  Money

                Select  @beforeMoney=currentMoney  from  deleted   -------（从deleted表中获取交易前的金额，从inserted表中获取交易后的余额）

                Select  @afterMoney=currentMoney  from  inserted   

                If  ABS（@afterMoney-@beforeMoney）>20000 ------交易金额是否>2万

                       Begin

                          Print '交易金额：'+convert（varchar（8），

                          ABS（@afterMoney-@beforeMoney）)

                        Raiserror ('每笔交易不能超过2万元,交易失败',16,1)

                        Rollback  transaction         ------------回滚事务,撤销交易

                        End

            Go

69. 列级update触发器

         Create  trigger  trig_update_transIfo

         ON  transInfo

         For  update

         As

             If  update（transDate）

             Begin                -------检查是否修改了交易日期列transDate

             Print  '交易失败......'

             Raiserror（'安全警告：交易日期不能修改，由系统自动产生'，16，1）

             Rollback  transaction          ----------回滚事务，撤销交易

             End

         GO

70.数据库阶段数据管理的主要特点：1.采用数据模型表示复杂的数据结构；2.有较高的数据独立性；3.为用户提供了方便的用户接口；4.提供数据控制功能；5.增加系统 的灵活性。

71.   DB(数据库)：存储在计算机内有组织的、统一管理的相关数据的集合。

  DBMS(数据库管理系统)：位于用户与操作系统之间的数据管理软件。

  DBS(数据库系统)：是实现有组织的、动态的存储大量关联数据，以方便用户访问计算就硬件、软件和株距资源组成的计算机系统。

72.物理数据独立性和逻辑数据独立性：

       物理数据独立性：内模式的修改，只需对模式/内模式映象做相应修改，而不需修改        概念模式。逻辑数据独立性：概念模式的修改，只需对外模式/模式映象做相应修改，而不

需修改外模式。

73.  DBMS的主要功能：

        数据定义（DDL）：三级结构、两级映象、完整性约束等。数据操纵（DML）：检索、更新。数据保护：数据恢复、并发控制、完整性控制、安全性控制等。数据维护：数据载入、转换、存储、、性能监控等。数据字典（DD）：存储三级结构定义的数据库称为DD，即系统数据库。DBMS主要功能：数据库定义功能、数据存取功能、数据库运行管理功能、数据库的建立和维护功能。

74.--1、创建存储过程--

if exists (select * from sysobjects where name='Sum_wage')

drop procedure Sum_wage

GO

create procedure Sum_wage 

@PWage int,

@AWage int,

@total int

as 

while (1=1)

begin

if (select count(*) from ProWage)>2*(select count(*) from ProWage where Wage>=@PWage)

update ProWage set @total=@total+@AWage,Wage=Wage+@AWage

else

break

end

print'一共加薪：'+convert(varchar,@total)+'元'

print'加薪后的程序员工资列表：'

select * from ProWage

--调用存储过程1--

exec Sum_wage @PWage=2000,@AWage=100,@total=0

exec Sum_wage @PWage=2200,@AWage=100,@total=0

exec Sum_wage @PWage=3000,@AWage=100,@total=0

exec Sum_wage @PWage=4000,@AWage=100,@total=0

exec Sum_wage @PWage=5000,@AWage=100,@total=0

exec Sum_wage @PWage=6000,@AWage=100,@total=0

75.

if exists (select * from sysobjects where name='Avg_wage')

drop procedure Avg_wage

GO

create procedure Avg_wage 

@PWage int,

@AWage int,

@total int

as 

while (1=1)

begin

if ((select Avg(Wage) from ProWage)<=@PWage)

update ProWage set @total=@total+@AWage,Wage=Wage+@AWage

else

break

end

print'一共加薪：'+convert(varchar,@total)+'元'

print'加薪后的程序员工资列表：'

select * from ProWage

--调用存储过程--

exec Avg_wage @PWage=3000,@AWage=200,@total=0

exec Avg_wage @PWage=4500,@AWage=200,@total=0

76.代码：

INSERT INTO studentinformation(S_name,S_no,S_grade,S_address)

VALUES('花无影',25,'软件0802班','河北')

 

select studentinformation.S_name,studentinformation.S_no

into information2

from studentinformation

select S_no,S_grade,S_name

from studentinformation

where S_school='软件学院'

order by S_no

select S_no,S_name,S_class

from studentinformation

where S_sex='男'and S_name<>'花无影'

order by S_no

SELECT top 20 percent sname as 姓名,sno as 学号,saddress as 地址

FROM  yuan

WHERE  sschool ='软件学院'

ORDER BY  sno asc,sname desc

select charindex('data','my data is set',1)    （data的索引位置）

select len('java xuexibiji')      （字符串长度）

select lower('BAO DING DIANLI ZHI YE JI SHU XUE YUAN')   （转换成小写）

select replace('软件学院','软件学院','软件工程学院')  （替换）

select stuff('a b c d e f g h i j k l m n',3,6,'今天的学习内容')   （从第三字符个开始替换，替换6个字符）

select getdate()   （当前日期）

select dateadd(dd,250,'04/12/2009')   （250天后的日期）

select datediff(mm,'04/20/2005','08/05/2010')   （相差的月数）

select datename(dw,'04/12/2009')    （返回星期几）

select datepart(dd,'04/12/2009')    (返回当天是几号)

select abs(-10)   绝对值

select ceiling(-32.8)    返回大于或等于该数的最小整数

select power(102,3)   取数值表达式的幂值

select round(3.141592653,4)     (?)将数值表达式四舍五入成指定精度

select sign(1000)   正数返回1，负数返回-1，0返回0.

select sqrt(6.25)      //返回该数据的平方根

select convert(varchar(100),45678932) //转变数据类型  

select current_user      //返回当前用户的名字

select datalength('软件学院')    //返回字符长度

select host_name()　　　//返回用户登录的计算机名

select system_user      //返回登录的用户名

select user_name(1)     //从任意数据库中返回dbo(jizhu)

create proc pro_stu

@scorepass float

as

print '学号'+convert(varchar(5),@scorepass)

select name

from stuent

where student.score=@scorepass

go

exec pro_stu @scorepass=85

76.导出SQL Server数据库中数据：

declare @objectId int 

set @objectId=object_id('sun') 

if @objectId is not null 

begin 

    drop proc sun 

end 

go 

create proc sun (@tablename varchar(256)) 

as 

begin 

    set nocount on 

    declare @sqlstr varchar(4000) 

    declare @sqlstr1 varchar(4000) 

    declare @sqlstr2 varchar(4000) 

    select @sqlstr='select ''insert '+@tablename 

    select @sqlstr1='' 

    select @sqlstr2='(' 

    select @sqlstr1='values (''+' 

    select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case 

    when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end' 

    when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end' 

    when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end' 

    when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end' 

    when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end' 

    when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end' 

    when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end' 

    when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end' 

    when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end' 

    when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end' 

    when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end' 

    when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end' 

    when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end' 

    when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end' 

    when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end' 

    when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end' 

    when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end' 

    when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end' 

    else '''NULL''' 

    end as col,a.colid,a.name 

    from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36 

    )t order by colid 

    select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename 

    print @sqlstr 

    exec( @sqlstr) 

    set nocount off 

end 

go
————————————————
版权声明：本文为CSDN博主「心在忍耐」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/jason_ldh/article/details/9881541





